﻿/*
Created: 16.10.2011
Modified: 29.10.2011
Project: Softver za kucne finansije
Model: Microsoft SQL Server 2008
Author: Armin
Version: 0.7
Database: MS SQL Server 2008
*/

-- Create tables section -------------------------------------------------

-- Table Korisnik

CREATE TABLE [Korisnik]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Username] Char(100) NOT NULL,
 [Password] Char(10) NOT NULL,
 [FK_ID_Budzet] Int NOT NULL,
  CONSTRAINT [CH_Korisnik_LEN_Password] CHECK (LEN(Password) >= 6)
)
go

-- Add keys for table Korisnik

ALTER TABLE [Korisnik] ADD CONSTRAINT [PK_Korisnik] PRIMARY KEY ([ID])
go

ALTER TABLE [Korisnik] ADD CONSTRAINT [UQ_Korisnik_Username] UNIQUE ([Username])
go

-- Table Transakcija

CREATE TABLE [Transakcija]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Datum] Date NOT NULL,
 [Vrijednost] Money NOT NULL,
 [Opis] Varchar(max) NULL,
 [FK_ID_Korisnik] Int NOT NULL,
 [FK_ID_Kategorija] Int NOT NULL,
 [FK_ID_TIp] Int NOT NULL,
 [FK_ID_Vrsta] Int NOT NULL
)
go

-- Add keys for table Transakcija

ALTER TABLE [Transakcija] ADD CONSTRAINT [PK_Transakcija] PRIMARY KEY ([ID])
go

-- Table Tip_Transakcije

CREATE TABLE [Tip_Transakcije]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Naziv] Char(10) NOT NULL
)
go

-- Add keys for table Tip_Transakcije

ALTER TABLE [Tip_Transakcije] ADD CONSTRAINT [PK_Tip_Transakcije] PRIMARY KEY ([ID])
go

ALTER TABLE [Tip_Transakcije] ADD CONSTRAINT [UQ_Tip_transakcije_Naziv] UNIQUE ([Naziv])
go

-- Table Kategorija

CREATE TABLE [Kategorija]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Naziv] Char(100) NOT NULL,
 [FK_ID_Grupa] Int NOT NULL
)
go

-- Add keys for table Kategorija

ALTER TABLE [Kategorija] ADD CONSTRAINT [PK_Kategorija] PRIMARY KEY ([ID])
go

ALTER TABLE [Kategorija] ADD CONSTRAINT [UQ_Naziv_Kategorija] UNIQUE ([Naziv])
go

-- Table Vrsta

CREATE TABLE [Vrsta]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Naziv] Char(10) NOT NULL
)
go

-- Add keys for table Vrsta

ALTER TABLE [Vrsta] ADD CONSTRAINT [PK_Vrsta] PRIMARY KEY ([ID])
go

ALTER TABLE [Vrsta] ADD CONSTRAINT [UQ_Naziv_Vrsta] UNIQUE ([Naziv])
go

-- Table Budzet

CREATE TABLE [Budzet]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Stanje] Money NOT NULL
)
go

-- Add keys for table Budzet

ALTER TABLE [Budzet] ADD CONSTRAINT [PK_Budzet] PRIMARY KEY ([ID])
go

-- Table Grupa

CREATE TABLE [Grupa]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Naziv] Char(100) NOT NULL
)
go

-- Add keys for table Grupa

ALTER TABLE [Grupa] ADD CONSTRAINT [PK_Grupa] PRIMARY KEY ([ID])
go

ALTER TABLE [Grupa] ADD CONSTRAINT [UQ_Grupa_Naziv] UNIQUE ([Naziv])
go

-- Create procedures section -------------------------------------------------

CREATE PROCEDURE [sp_Transakcija_INSERT]
@datum Date,
@vrijednost Money,
@opis Varchar(max),
@korisnik Int, 
@kategorija Int, 
@tip Int, 
@vrsta Int
AS
BEGIN
    INSERT INTO Transakcija (Datum, Vrijednost, Opis, FK_ID_Korisnik, 
                            FK_ID_Kategorija, FK_ID_Tip, FK_ID_Vrsta)
    VALUES (@datum, @vrijednost, @opis, @korisnik, @kategorija, @tip, @vrsta)
END
go

CREATE PROCEDURE [sp_Korisnik_INSERT]
@username Char(100),
@password Char(10),
@budzet Int
AS
BEGIN
    INSERT INTO Korisnik (Username, Password, FK_ID_Budzet)
    VALUES (@username, @password, @budzet)
END
go

CREATE PROCEDURE [sp_Korisnik_UPDATE]
@username Char(100),
@password Char(10)
AS
BEGIN
   UPDATE Korisnik
   SET Password = @password
   WHERE Username = @username 
END
go

CREATE PROCEDURE [sp_Korisnik_DELETE]
@username Char(100)
AS
BEGIN
    DELETE FROM Korisnik
    WHERE Username = @username
END
go

CREATE PROCEDURE [sp_Transakcija_UPDATE]
@datum Date,
@vrijednost Money,
@opis Text,
@id Int
AS
BEGIN
    UPDATE Transakcija
    SET Datum = @datum,
        Vrijednost = @vrijednost,
        Opis = @opis    
    WHERE ID = @id 
END
go

CREATE PROCEDURE [sp_Transakcija_DELETE]
@id Int
AS
BEGIN
    DELETE FROM Transakcija
    WHERE ID = @id    
END
go

CREATE PROCEDURE [sp_Transakcija_SELECT_Sve]
AS
BEGIN
    SELECT * FROM Transakcija
END
go

CREATE PROCEDURE [sp_Budzet_UPDATE]
@stanje Money,
@id Int
AS
BEGIN
    UPDATE Budzet
    SET Stanje = @stanje       
    WHERE ID = @id 
END
go

CREATE PROCEDURE [sp_Budzet_DELETE]
@id Int
AS
BEGIN
    DELETE FROM Budzet
    WHERE ID = @id
END
go

CREATE PROCEDURE [sp_Kategorija_INSERT]
@naziv Char(100),
@grupa Int
AS
BEGIN
    INSERT INTO Kategorija (Naziv, FK_ID_Grupa)
    VALUES (@naziv, @grupa)
END
go

CREATE PROCEDURE [sp_Kategorija_UPDATE]
@naziv Char(100)
@id Int
AS
BEGIN
    UPDATE Kategorija
    SET Naziv = @naziv
    WHERE ID = @id 
END
go

CREATE PROCEDURE [sp_Kategorija_DELETE]
@id Int
AS
BEGIN
    DELETE FROM Kategorija
    WHERE ID = @id
END
go

CREATE PROCEDURE [sp_Grupa_INSERT]
@naziv Char(100)
AS
BEGIN
    INSERT INTO Grupa (Naziv)
    VALUES (@naziv)
END
go

CREATE PROCEDURE [sp_Grupa_UPDATE]
@id Int
@naziv
AS
BEGIN
    UPDATE Grupa
    SET Naziv = @naziv
    WHERE ID = @id 
END
go

CREATE PROCEDURE [sp_Grupa_DELETE]
@id Int
AS
BEGIN
    DELETE FROM Grupa
    WHERE ID = @id
END
go

CREATE PROCEDURE [sp_Transakcija_SELECT_PoGrupi]
@grupa Char(100)
AS
BEGIN
    SELECT Transakcija.Datum, Transakcija.Vrijednost, Transakcija.Opis, 
           Vrsta.Naziv AS Vrsta
    FROM Transakcija, Kategorija, Grupa, Vrsta, Tip_Transakcije
    WHERE Transakcija.FK_ID_Kategorija = Kategorija.ID 
    AND Kategorija.FK_ID_Grupa = Grupa.ID
    AND Transakcija.FK_ID_Vrsta = Vrsta.ID
    AND Grupa.Naziv = @grupa 
END
go

CREATE PROCEDURE [sp_Transakcija_SELECT_PoKategoriji]
@kategorija Char(100)
AS
BEGIN
    SELECT Transakcija.Datum, Transakcija.Vrijednost, Transakcija.Opis, 
           Vrsta.Naziv AS Vrsta
    FROM Transakcija, Kategorija, Grupa, Vrsta
    WHERE Transakcija.FK_ID_Kategorija = Kategorija.ID
    AND Transakcija.FK_ID_Vrsta = Vrsta.ID
    AND Kategorija.FK_ID_Grupa = Grupa.ID
    AND Kategorija.Naziv = @kategorija 
END
go

-- Create views section -------------------------------------------------

CREATE VIEW [View_Main]
AS SELECT Transakcija.Datum, Transakcija.Vrijednost, Transakcija.Opis, 
          Vrsta.Naziv
    FROM Transakcija, Vrsta
    WHERE Transakcija.FK_ID_Vrsta = Vrsta.ID
go

-- Create relationships section ------------------------------------------------- 

ALTER TABLE [Transakcija] ADD CONSTRAINT [izvrsava] FOREIGN KEY ([FK_ID_Korisnik]) REFERENCES [Korisnik] ([ID]) ON DELETE CASCADE
go

ALTER TABLE [Transakcija] ADD CONSTRAINT [pripada] FOREIGN KEY ([FK_ID_Kategorija]) REFERENCES [Kategorija] ([ID]) ON DELETE NO ACTION
go

ALTER TABLE [Transakcija] ADD CONSTRAINT [tipa] FOREIGN KEY ([FK_ID_TIp]) REFERENCES [Tip_Transakcije] ([ID])
go

ALTER TABLE [Transakcija] ADD CONSTRAINT [je] FOREIGN KEY ([FK_ID_Vrsta]) REFERENCES [Vrsta] ([ID])
go

ALTER TABLE [Kategorija] ADD CONSTRAINT [sadrzava] FOREIGN KEY ([FK_ID_Grupa]) REFERENCES [Grupa] ([ID]) ON DELETE NO ACTION
go

ALTER TABLE [Korisnik] ADD CONSTRAINT [je_vlasnik] FOREIGN KEY ([FK_ID_Budzet]) REFERENCES [Budzet] ([ID])
go




